package com.roc.web.action.terminal;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class WareHouseSNStatisticsSearchCondition extends BaseSearchCondition {
	
	private String companyName;// 分公司

	private String  endBrandDate ;// 绑定结束时间(年月日)
	
	private boolean isSlsc ;// 是否受理市场总部
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {

		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		/*
		if(Constance.isNotEmpty(companyName)){
			filedBeanList.add(new WhereHQLFieldBean("M1.", mchtId, "=", false));
		}
		
		
		if(Constance.isNotEmpty(termCode)){
         	filedBeanList.add(new WhereHQLFieldBean("M1.term_code",termCode.trim(),"=", false));
       	}
		*/
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {

		StringBuffer sb = new StringBuffer();
		
		sb.append("   SELECT COUNT(1) ");
		sb.append("     FROM (SELECT 1 ");
		sb.append("             FROM (SELECT B1.AGENT_ID, I1.SERIAL_NUM, I1.POS_BRAND, I1.POS_NAME ");
		sb.append("                     FROM APSMGM.YJ_POS_BATCH B1, APSMGM.YJ_POS_BATCH_INFO I1 ");
		sb.append("                    where B1.BATCH_NO = I1.BATCH_NO) TT, ");
		sb.append("                  APSMGM.T_OPERATORS T1 ");
		sb.append("            WHERE TT.AGENT_ID = T1.ID ");
		
		// 如果是受理市场总部，只查直营
		if ( isSlsc ){
			sb.append("    	 AND T1.GROUP_CD = '2' AND T1.ROLE_TYPE IN ('02','03') ");
		}
		sb.append("            GROUP BY T1.COMPANYID, TT.POS_BRAND, TT.POS_NAME ");

		if(Constance.isNotEmpty(companyName)){
			sb.append("   HAVING EXISTS (select 1 ");
			sb.append("                    from APSMGM.TBL_AREA A2 ");
			sb.append("                   WHERE T1.COMPANYID = A2.ID ");
			sb.append("                     AND A2.NAME = '" + companyName + "') ");
		}
		sb.append("   ) ");
		
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
		sb.append("   SELECT (select A1.NAME from APSMGM.TBL_AREA A1 WHERE A1.ID = T1.COMPANYID), ");
		sb.append("          TT.POS_BRAND, ");
		sb.append("          TT.POS_NAME, ");
		sb.append("          SUM(TT.ALL_COUNT) ALL_COUNTS, ");
		sb.append("          SUM(TT.BRAND_COUNT) BRAND_COUNTS, ");
		sb.append("          SUM(TT.TD_BRAND_COUNT) TD_BRAND_COUNTS ");
		sb.append("    ");
		sb.append("     FROM (SELECT BB.*, ");
		sb.append("                  1 ALL_COUNT, ");
		sb.append("                  (CASE ");
		sb.append("                    WHEN T.REC_UPD_TS IS NOT NULL THEN ");
		sb.append(" 1 ");
		sb.append("                    ELSE ");
		sb.append(" 0 ");
		sb.append("                  END) BRAND_COUNT, ");
		sb.append("                  (CASE ");
		sb.append("                    WHEN T.REC_UPD_TS IS NOT NULL AND ");
		sb.append("                         TO_CHAR(T.REC_UPD_TS, 'yyyyMMdd') = " + endBrandDate + " THEN ");
		sb.append(" 1 ");
		sb.append("                    ELSE ");
		sb.append(" 0 ");
		sb.append("                  END) TD_BRAND_COUNT  ");
		sb.append("             FROM (SELECT B1.AGENT_ID, ");
		sb.append("                          I1.SERIAL_NUM, ");
		sb.append("                          I1.POS_BRAND, ");
		sb.append("                          I1.POS_NAME ");
		sb.append("                     FROM APSMGM.YJ_POS_BATCH B1, APSMGM.YJ_POS_BATCH_INFO I1 ");
		sb.append("                    where B1.BATCH_NO = I1.BATCH_NO) BB ");
		sb.append("             LEFT JOIN APSMGM.TBL_TERM_INF T ON BB.SERIAL_NUM = T.DEVICE_CD ");
		sb.append("                                            AND BB.POS_BRAND = T.TMNL_BRAND ");
		sb.append("                                            AND INSTR(T.DEVICE_CD, 'YJ') <= 0 ");
		sb.append("                                            AND T.REC_UPD_TS IS NOT NULL ");
		sb.append("                                            AND TO_CHAR(T.REC_UPD_TS, ");
		sb.append("                                                        'yyyyMMdd') <= ");
		sb.append("                                                " + endBrandDate + " ) TT, ");
		sb.append("          APSMGM.T_OPERATORS T1 ");
		sb.append("    WHERE TT.AGENT_ID = T1.ID ");
		
		// 如果是受理市场总部，只查直营
		if ( isSlsc ){
			sb.append("    	 AND T1.GROUP_CD = '2' AND T1.ROLE_TYPE IN ('02','03') ");
		}
		
		sb.append("    GROUP BY T1.COMPANYID, TT.POS_BRAND, TT.POS_NAME ");

		if(Constance.isNotEmpty(companyName)){
			sb.append("   HAVING EXISTS (select 1 ");
			sb.append("                    from APSMGM.TBL_AREA A2 ");
			sb.append("                   WHERE T1.COMPANYID = A2.ID ");
			sb.append("                     AND A2.NAME = '" + companyName + "') ");
		}
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return " ORDER BY T1.COMPANYID, TT.POS_BRAND, TT.POS_NAME ";
	}

	public String getCompanyName() {
		return companyName;
	}

	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}

	public String getEndBrandDate() {
		return endBrandDate;
	}

	public void setEndBrandDate(String endBrandDate) {
		this.endBrandDate = endBrandDate;
	}

	public boolean isSlsc() {
		return isSlsc;
	}

	public void setSlsc(boolean isSlsc) {
		this.isSlsc = isSlsc;
	}

}
